<?
session_start();
include 'connect.php';
include 'functions.php';

$user = mysql_real_escape_string($_POST['user']);
$slevel = mysql_real_escape_string($_POST['slevel']);
$elevel = mysql_real_escape_string($_POST['elevel']);
$job = mysql_real_escape_string($_POST['job']);
$amount = mysql_real_escape_string($_POST['amount']);
$filter = mysql_real_escape_string($_POST['filter']);
$direction = mysql_real_escape_string($_POST['direction']);

if($amount == ""){
	$amount = 12;
}
if($direction == ""){
	$direction  = "DESC";
}
if($filter == ""){
	$orderBy = " ORDER BY spec.udate DESC";
}else{
	if($filter == "rate"){
		$orderBy = " ORDER BY (rate.value/rate.count) ".$direction;
	}else{
		$orderBy = " ORDER BY ".$filter." ".$direction;
	}
}
$output = "
		<html>
			<head>	 	
				<!-- Query results Window -->

			</head>
			<body>
				<script>		
					var filterDir = '".$direction."';
					
					
					$(document).ready(function(){
						$('.show-spec-row').click(function(e) {	
							var clicked = jQuery(e.target);
							var id = clicked.attr('id');
							editSpec(id);
						});
						
						$('.show-user').click(function(e) {	
							var clicked = jQuery(e.target);
							var id = clicked.attr('id');
							getUser(id);
						});
						
						$('.filter-col').click(function(e) {	
							var clicked = jQuery(e.target);
							var id = clicked.attr('id');
							if(filterDir == 'DESC'){
								filterDir = 'ASC'
							}else{
								filterDir = 'DESC'
							}
							query(".$amount.", id, filterDir);
						});
						
						$('.more-button').click(function(e) {	
							query(".($amount+10).", '".$filter."', '".$direction."');
						});
						
						showArrow();
					});
					
					function showArrow(){
						$('.arrow').hide();
						
						var paramsUp = {
								'width': '0',
								'height': '0',
								'border-left': '5px solid transparent',
								'border-right': '5px solid transparent',
								'border-bottom': '5px solid #0368ad'
						}
						var paramsDown = {
								'width': '0',
								'height': '0',
								'border-left': '5px solid transparent',
								'border-right': '5px solid transparent',
								'border-top': '5px solid #0368ad'
						}
						if(filterDir == 'DESC'){
							var params = paramsDown;
						}else{
							var params = paramsUp;
						}
						if ('".$filter."' == ''){
							$('#a_spec_udate').css(params);
							$('#a_spec_udate').fadeIn('fast');
						}else{
							var fil = '".$filter."'
							fil = '#a_'+fil.replace('.','_');
							$(fil).css(params);
							$(fil).fadeIn('fast');
						}
						
					}
					
					$(function(){
						$('.ratings').children().not(':radio').hide();
						$('.ratings').stars({
							disabled: true
						});
					});
				</script>
				<table id='me_specs' class='content-subtitle-table'>";
				
$abfrage = "SELECT spec.specId
				   ,spec.userId
				   ,spec.layout
				   ,spec.jobId
				   ,spec.level
				   ,job.tri as jobTri
				   ,job.name as jobName
				   ,spec.description
				   ,spec.public
				   ,spec.cdate
				   ,DATE_FORMAT(spec.udate,'%d-%c-%Y') as udate
				   ,user.userName
				   ,user.avatar
				   ,rate.value
				   ,rate.count
			FROM (((
			ffxivc_specs as spec 
			LEFT JOIN 
			ffxivc_jobs as job
			ON job.jobId = spec.jobId)
			LEFT JOIN ffxivc_users as user
			ON user.userId = spec.userId)
			LEFT JOIN (SELECT specId, sum(rat.value) as value, count(rat.rateId) as count FROM ffxivc_ratings as rat group by rat.specId) as rate
			ON spec.specId = rate.specId)
		WHERE spec.public = 'Y' 
			AND spec.level >= \"".$slevel."\"
			AND spec.level <= \"".$elevel."\"
		";			

if($_POST['user'] != ""){
	$abfrage .= " AND user.userName = \"".$user."\"";
}	
if($_POST['job'] != "none" && $_POST['job'] != ""){
		$abfrage .= " AND job.name = \"".$job."\"";
}
$abfrage .= $orderBy;	
$abfrage .= " LIMIT 0, ".($amount+1);

$res = mysql_query($abfrage);
	
if ($res){
		
	$num = mysql_num_rows($res);	
	$i = 1;
	if($num > 0){
		$output .= "<tr class='spec-row tr-table border-bottom'> 
						<td class='td-table vertical-middle pad-5 pad-b-10'>
							<div id='userName' class='filter-col normal-text bold-text blue-text cur-point fl-left' style='width: 90px;overflow: hidden;height: 15px;' >User</div>
							<div class='fl-right' style='padding-top:5px;'><div id='a_userName' class='arrow'></div></div>
						</td>
						<td class='td-table vertical-middle pad-5 pad-b-10' style='padding-left: 20px;'>
							<div id='spec.level' class='filter-col normal-text bold-text blue-text cur-point fl-left' >Level</div>
							<div class='fl-right' style='padding-top:5px;'><div id='a_spec_level' class='arrow'></div></div>
						</td>
						<td class='td-table vertical-middle pad-5 pad-b-10'>
							<div id='job.name' class='filter-col normal-text bold-text blue-text cur-point fl-left' >Class</div>
							<div class='fl-right' style='padding-top:5px;'><div id='a_job_name' class='arrow'></div></div>
						</td>
						<td class='td-table vertical-middle pad-5 pad-lr-20 pad-b-10'>
							<div id='spec.description' class='filter-col normal-text bold-text blue-text cur-point fl-left' style='max-height: 40px;overflow: hidden;'>Description</div>
							<div class='fl-right' style='padding-top:5px;'><div id='a_spec_description' class='arrow'></div></div>
						</td>
						<td class='td-table vertical-middle pad-5 pad-b-10 cur-point'>
							<div id='rate' class='filter-col normal-text bold-text blue-text cur-point fl-left' >Rating</div>
							<div class='fl-right' style='padding-top:5px;'><div id='a_rate' class='arrow'></div></div>
						</td>
						<td  class='td-table vertical-middle pad-5 pad-b-10' name='no' colspan='2'>
							<div id='spec.udate' class='filter-col normal-text bold-text blue-text cur-point fl-left' style='width: 70px;'>Last update</div>
							<div class='fl-right' style='padding-top:5px;'><div id='a_spec_udate' class='arrow'></div></div>
						</td>
					</tr>";
	}else{
		$output .= "<tr class='tr-table'> 
						<td class='td-table vertical-middle pad-5 pad-b-10' colspan='6'> 
							<div class='normal-text' >No specs found</div>
						</td>
					</tr>";
	}

	while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
		/*if($row['avatar'] != ""){
			$MyAvatar = "<div class='border-av'><img class='avatar-pic' src='".$row['avatar']."?".time()."'/></div>";
		}else{
			$MyAvatar = "<div class='border-av'><img class='avatar-pic' src='media/no-avatar.png'/></div>";
		}*/
		$output .= "<tr id='tr_".$row['specId']."' class='spec-row tr-table";
					if($i != $amount && $i!=$num){
						$output .= " border-bottom ";
					}
					
					$count = $row['count'];
					$value = $row['value'];
					if($count != 0){
						$average = floor($value / $count);
					}else{
						$average = 0;
					}
			$output .= "' name='".$row['specId']."'>
						<td class='td-table vertical-middle pad-5'>
							<div style='height:15px;'>
								<div id='".$row['userName']."' class='show-user cur-point normal-text blue-text' style='width: 90px;overflow: hidden;height: 15px;' >".$row['userName']."</div>
							</div>
						</td>
						<td class='td-table vertical-middle pad-5' style='padding-left: 20px'><div class='normal-text' style='width:46px;'>".$row['level']."</div></td>
						<td class='td-table vertical-middle pad-5'><img style='margin-right: 12px;' src='media/jobIcons/B_".$row['jobTri'].".png' /></td>
						<td class='td-table vertical-middle pad-5 pad-lr-20'><div class='normal-text' style='width:279px;max-height: 40px;overflow: hidden;'>".$row['description']."</div></td>
						<td class='td-table vertical-middle pad-5'>
								<form class='ratings' action='' method='post' style='width: 80px;' title='Rating: ".$average.", Votes: ".$row['count']."'>";

								foreach (get_options() as $id => $title){
									$output .= 	"<input type='radio' name='rate'  value='".$id."' id='rate".$id."'";
									if($id==$average){
										$output .= 	" checked";
									}
									$output .= 	" />";
								}
						
					$output .= 	"</form>
						</td>
						<td  class='td-table vertical-middle pad-5' name='no'>
							<div class='normal-text' style='width: 70px;'>".$row['udate']."</div>
						</td>
						<td  class='td-table vertical-middle pad-5' name='no'>
							<div id='".$row['specId']."' class='show-spec-row normal-text bold-text blue-text font-10 cur-point'>Show</div>
						</td>
					</tr>";
		if($i == $amount){
			break;
		}
		$i++;
	}
	
	if($i<$num){
		// MORE COMMENTS TO SHOW
		$output .= "<tr>
			<td colspan='7' style='text-align: center;padding-top: 8px;'>
				<div class='more-button cur-point arrow-down center' style='display:inline-block;'/>
			</td>
		</tr>";
		
	}
		

}else{
	echo mysql_error();
}
$output .= "</table>
			<div style='clear:both;'></div>
			</body>
		</html>";
echo $output; 

?>